package com.decent.ejfadmin.user.dao;

import com.decent.ejfadmin.common.bean.SupCertificateDto;
import com.decent.ejfadmin.common.bean.SupUser;
import com.decent.ejfadmin.user.bean.OssPicUrlBean;
import com.decent.ejfadmin.user.bean.SupCertificateLog;
import com.decent.ejfadmin.user.bean.SupCertificateRecordDto;
import com.decent.ejfadmin.user.dao.provider.UserCertificateProvider;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * describe:会员实名认证管理
 *
 * @author JunLei He
 * @date 2018年10月13日
 */
@Mapper
@Repository
public interface UserCertificateDao {

	/**
	 * 查询会员实名认证信息列表
	 *
	 * @param supUser
	 * @param supCertificateDto
	 * @return
	 */
	@SelectProvider(type = UserCertificateProvider.class, method = "queryUserCertificateList")
	List<SupCertificateDto> queryUserCertificateList(@Param("supUser") SupUser supUser,
			@Param("supCertificateDto") SupCertificateDto supCertificateDto);

	/**
	 * 查询支付宝图片
	 *
	 * @param id 支付宝id
	 * @return 返回图片
	 */
	@Select(" SELECT " + " c.reverse_pic_url reversePicUrl, " + " c.handing_pic_url handingPicUrl, "
			+ " c.front_pic_url frontPicUrl, " + " c.e_signature eSignature " + "FROM " + " yuhuang_sup_certificate c "
			+ "WHERE " + " c.id = #{id} ")
	OssPicUrlBean queryLevelEsignPic(@Param("id") Long id);

	/**
	 * 根据id查询实名认证信息
	 *
	 * @param id 支付宝id
	 * @return 返回信息
	 */
	@Select("SELECT " + " c.user_id userId, " + " c.alipay, " + " c.biz_no bizNo, " + " c.cert_level certLevel, "
			+ " c.level_state levelState, " + " c.success_level successLevel," + " front_pic_url frontPicUrl " + "FROM "
			+ " `yuhuang_sup_certificate` c " + "WHERE " + " c.id = #{id} ")
	SupCertificateDto queryUserCertById(@Param("id") Long id);

	/**
	 * 修改实名认证状态
	 *
	 * @param id     实名认证id
	 * @param status 状态
	 * @param remark 备注
	 * @return
	 */
	@UpdateProvider(type = UserCertificateProvider.class, method = "updateUserCertificate")
	int updateUserCertificate(@Param("id") Long id, @Param("status") Integer status, @Param("remark") String remark);

	/**
	 * 修改当前申请登记和当前等级认证状态
	 *
	 * @param certLevel  当前认证等级
	 * @param levelState 当前等级认证状态
	 * @param id         实名认证id
	 * @return
	 */
	@Update("update yuhuang_sup_certificate SET  cert_level=#{certLevel},level_state  = #{levelState} where id=#{id}")
	int updateUserCertificateState(@Param("id") Long id, @Param("certLevel") Integer certLevel,
			@Param("levelState") Integer levelState);

	/**
	 * @param account 会员账号
	 * @return
	 */
	@Select(" SELECT " + " c.cert_level certLevel, " + " c.level_state levelState, " + " c.success_level successLevel "
			+ "FROM " + " `yuhuang_sup_certificate` c, " + " yuhuang_sup_user u " + "WHERE " + " u.user_id = c.user_id "
			+ "AND u.account = #{account} ")
	SupCertificateDto queryUserCertByAccount(@Param("account") String account);

	/**
	 * 实名认证记录
	 *
	 * @param supCertificateRecordDto 实名认证记录信息
	 * @return 返回成功条数
	 */
	@InsertProvider(type = UserCertificateProvider.class, method = "createSupCertificateRecord")
	int createSupCertificateRecord(@Param("supCertificateRecordDto") SupCertificateRecordDto supCertificateRecordDto);

	/**
	 * 统计今日成功总金额
	 *
	 * @param userId
	 * @return
	 */
	@Select(" select IFNULL(SUM(settlePrice),0) settlePrice from (SELECT  userId,IFNULL(SUM(c.settlePrice),0) as settlePrice "
			+ "FROM  yuhuang_supply_card C WHERE  "
			+ "c.startTime >= DATE_FORMAT(NOW(), '%Y-%m-%d')  and c.`status` = 1  "
			+ "group by c.userId) t where t.userId = #{userId} ")
	int queryCardSuccessNum(@Param("userId") Long userId);

	/**
	 * 新增实名认证备注
	 *
	 * @param id     表id
	 * @param remark 备注
	 * @return
	 */
	@Update("update yuhuang_sup_certificate set remark  = CONCAT(ifnull(remark,''),#{remark}) where id  = #{id}")
	int addRemark(@Param("id") Long id, @Param("remark") String remark);

	/**
	 * 绑定支付宝
	 *
	 * @param userId
	 * @param aliPay
	 * @return
	 */
	@Insert("insert yuhuang_sup_alipay (user_id,alipay,gmt_create,gmt_modified) values (#{userId},#{aliPay},now(),now())")
	int insertAliPay(@Param("userId") Long userId, @Param("aliPay") String aliPay);

	/**
	 * 新增认证操作记录
	 *
	 * @param supCertificateLog
	 * @return
	 */
	@Insert("insert into yuhuang_sup_certificate_log" + "(operator_name," + "create_time," + "certificate_id,"
			+ "trigger_type)" + "values(" + "#{supCertificateLog.operatorName}," + "now(),"
			+ "#{supCertificateLog.certificateId},#{supCertificateLog.triggerType})")
	int insertCertificateLog(@Param("supCertificateLog") SupCertificateLog supCertificateLog);

	/**
	 * 统计成功提现总金额 2024-08-20 ebitg 增加
	 * 
	 * @param userId
	 * @return
	 */
	@Select(" SELECT " + " IFNULL(SUM(c.price),0) " + "FROM " + " yuhuang_withdraw_record C " + "WHERE "
			+ " c.`state` = 1 " + "and c.userId = #{userId} ")
	int queryWithdrawSuccessSum(@Param("userId") Long userId);

	@Update("update yuhuang_sup_certificate set contract_id  = #{targetFilename}  where id  = #{id}")
	int updateUserCertificateContact(Long id, String targetFilename);
}
